package batch.bounce;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.lang.ObjectUtils;
import org.apache.commons.lang.StringUtils;

import org.apache.log4j.*;
import java.sql.Connection;
import java.util.List;

import java.sql.*;
import java.io.*;
import java.util.*;
import java.util.HashMap;
import java.lang.Integer;

public class emailBounce
{
    private String fileName = "";
    private String dataPath = "";
    //private final String ORAURL = "jdbc:oracle:thin:@eu-stgdb:1521:ORAEU";
    private final String ORAURL = "jdbc:oracle:thin:@eu-anydb:1521:ORAEU";

    public emailBounce() {
        ;
    }

    public static void main(String [] args) {
        emailBounce eBounce = null;

        if(args == null || args.length < 1 || args.length > 1) {
            System.out.println("java emailBounce Exception == Missed FileName Parameter");
            System.exit(-1);
        }
        else{
            eBounce = new emailBounce();
            eBounce.fileName = args[0];
        }

        Logger.getLogger("bounce.emailBounce").info("( * ) Start.");
        Logger.getLogger("bounce.emailBounce").info("fileName == " + eBounce.fileName);
        eBounce.startPG();
        Logger.getLogger("bounce.emailBounce").info("( * ) End.");
    }

    public void startPG(){
        PrintStream logForYear = null;
        File fForYear = null;

        String sYear = fileName.substring(0, 4);
        String sYrMM = fileName.substring(0, 6);

        String yearLogPath = "/home01/sfc/batch/bounce/log/year/";
        String yrLgFlNm = "log_" + sYear + ".csv";

        HashMap hData =  null;

        Connection conn = null;
        QueryRunner qRunner = null;

        String strEmail;

        int iNonUser = 0;
        int iUpUser = 0;

        int iRE = 0;

        dataPath = "/home01/sfc/batch/bounce/data/" + sYrMM + "/";

        StringBuffer sQuery = new StringBuffer("SELECT REG_COUNTRY, USER_NO FROM G_MB_MEMBER WHERE U_EMAIL = ? ");

        StringBuffer uQuery = new StringBuffer("UPDATE G_MB_MEMBER SET NEWS_LETTER = 'N' WHERE USER_NO = ? ");

        StringBuffer dQuery = new StringBuffer("DELETE FROM G_MB_OPTIN WHERE USER_NO = ? AND CODE = '0113001'");

        StringBuffer iQuery = new StringBuffer()
                             .append("INSERT INTO G_MB_OPTIN_HISTORY( SEQ_ID                          \n")
                             .append("                              , USER_NO                         \n")
                             .append("                              , CODE                            \n")
                             .append("                              , STATUS_CD                       \n")
                             .append("                              , HIS_CD)                         \n")
                             .append("                        VALUES(  SEQ_G_MB_OPTIN_HISTORY.NEXTVAL \n")
                             .append("                               ,  ?                             \n")
                             .append("                               , '0113001'                      \n")
                             .append("                               , 'N'                            \n")
                             .append("                               , '1003008')                     \n");

        try {
            hData = this.getInfo();

            if(hData == null && hData.size() < 1) {
                throw new Exception("No Data");
            }

            int iSize = hData.size();

            Class.forName("oracle.jdbc.driver.OracleDriver");
            conn = DriverManager.getConnection(ORAURL, "gsms", "any02gs");
            conn.setAutoCommit(false);

            qRunner = new QueryRunner();

            ResultSetHandler rsh = new ArrayListHandler();

            for(int i=0;i<iSize;i++) {
                strEmail = (String)hData.get(new Integer(i));

                List lsTemp = (List)qRunner.query(conn, sQuery.toString(), strEmail, rsh);

                if(lsTemp.size() > 0){
                    for(Iterator j = lsTemp.iterator(); j.hasNext();){
                        Object[] sTemp = (Object[])j.next();
                        String reg_country = ObjectUtils.toString(sTemp[0]);

                        if(   !reg_country.equals("gbr")
                           && !reg_country.equals("ger")
                           && !reg_country.equals("nld")
                           && !reg_country.equals("rus")
                           && !reg_country.equals("bel")
                           && !reg_country.equals("zaf")
                           && !reg_country.equals("ita")
                           && !reg_country.equals("prt")
                           && !reg_country.equals("nrd")
                           && !reg_country.equals("esp")
                           && !reg_country.equals("uan")){
                            iRE = qRunner.update(conn, uQuery.toString(), sTemp[1]);
                        }
                        else{
                            iRE = qRunner.update(conn, dQuery.toString(), sTemp[1]);

                            iRE = qRunner.update(conn, iQuery.toString(), sTemp[1]);
                        }

                        iUpUser++;
                    }
                }
                else{
                    iNonUser++;
                }

                if((i % 50) == 0) System.out.print(".");
            }

            conn.commit();

            Logger.getLogger("bounce.emailBounce").info("Total Count : " + iSize);
            Logger.getLogger("bounce.emailBounce").info("Update Count : " + iUpUser);
            Logger.getLogger("bounce.emailBounce").info("No User Count : " + iNonUser);

            fForYear = new File(yearLogPath + yrLgFlNm);

            //If this is the first writing, it will write down the title.
            if(!fForYear.exists()) {
                logForYear = new PrintStream(new FileOutputStream(yearLogPath + yrLgFlNm, true));
                logForYear.println("File, Total Count, Update Count");
            }
            else{
                logForYear = new PrintStream(new FileOutputStream(yearLogPath + yrLgFlNm, true));
            }

            logForYear.println(fileName + ", "+ iSize +", "+ iUpUser);  //Write down the log.
        }
        catch(Exception ex) {
            if(conn != null) try { conn.rollback(); } catch (Exception ee) {}
            ex.printStackTrace();

            Logger.getLogger("bounce.emailBounce").info(ex.toString());
            StackTraceElement[] stElement = ex.getStackTrace();
            for(int p=0; p<stElement.length;p++){
                Logger.getLogger("bounce.emailBounce").info(stElement[p].toString());
            }
        }
        finally {
            if(conn != null) try { conn.close(); } catch (Exception ee) { ee.printStackTrace();  }
            if(logForYear != null) try { logForYear.close(); } catch (Exception ee) { ee.printStackTrace();  }
        }
    }

    public HashMap getInfo() throws Exception {
        HashMap hMap = new HashMap();
        BufferedReader in = null;
        String line = "";
        int i = 0;

        try {
            in = new BufferedReader(new FileReader(dataPath + fileName));

            while((line = in.readLine()) != null){
                hMap.put(new Integer(i), StringUtils.stripToEmpty(line.toLowerCase()));
                i++;
            }
        }
        finally {
            if(in != null) try { in.close(); } catch(Exception e) {}
        }

        return hMap;
    }
}
